package com.example.docmanagement.repository;

import com.example.docmanagement.entity.Category;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 分类数据访问层
 */
@Repository
public interface CategoryRepository extends JpaRepository<Category, Long> {

       /**
        * 根据 slug 查找分类
        */
       Optional<Category> findBySlug(String slug);

       /**
        * 检查 slug 是否存在
        */
       boolean existsBySlug(String slug);

       /**
        * 查找根分类（无父分类）
        */
       List<Category> findByParentIsNullOrderBySortOrder();

       /**
        * 查找根分类（包含子分类，避免 N+1 查询）
        * 注意：一次性加载所有子分类，在内存中构建树形结构
        */
       @Query("SELECT c FROM Category c " +
                     "LEFT JOIN FETCH c.children " +
                     "WHERE c.parent IS NULL " +
                     "ORDER BY c.sortOrder")
       List<Category> findByParentIsNullWithChildrenOrderBySortOrder();

       /**
        * 查找子分类
        */
       List<Category> findByParentIdOrderBySortOrder(Long parentId);

       /**
        * 查找启用的分类
        */
       List<Category> findByIsActiveTrueOrderBySortOrder();

       /**
        * 查询所有分类（树形结构）
        * 使用递归 CTE（公用表表达式）
        */
       @Query(value = "WITH RECURSIVE category_tree AS (" +
                     "  SELECT *, 0 as level FROM doc_categories WHERE parent_id IS NULL " +
                     "  UNION ALL " +
                     "  SELECT c.*, ct.level + 1 FROM doc_categories c " +
                     "  INNER JOIN category_tree ct ON c.parent_id = ct.id" +
                     ") SELECT * FROM category_tree ORDER BY level, sort_order", nativeQuery = true)
       List<Category> findAllInTree();

       /**
        * 查询所有分类（包含父分类和子分类，避免 N+1 查询）
        * 一次性加载所有分类及其关联关系，在内存中构建树形结构
        */
       @Query("SELECT DISTINCT c FROM Category c " +
                     "LEFT JOIN FETCH c.parent " +
                     "LEFT JOIN FETCH c.children " +
                     "ORDER BY c.sortOrder")
       List<Category> findAllWithRelations();

       /**
        * 查询所有分类（扁平列表，使用构造函数表达式避免 N+1 查询）
        * 直接返回 DTO，支持数据库分页
        */
       @Query("SELECT new com.example.docmanagement.dto.response.CategoryResponse(" +
                     "c.id, c.name, c.slug, c.description, c.icon, " +
                     "p.id, c.sortOrder, c.docCount, c.isActive, " +
                     "c.createdAt, c.updatedAt) " +
                     "FROM Category c " +
                     "LEFT JOIN c.parent p " +
                     "ORDER BY c.sortOrder")
       List<com.example.docmanagement.dto.response.CategoryResponse> findAllFlatWithParent();
}
